package tsli.acm.dao;

import java.util.List;
import java.util.Map;

import tsli.acm.database.Database;

public class GAAgentStructureDAO {
	private Database db;
    
	 public GAAgentStructureDAO(Database db) {
		 this.db = db;
	 }
	 
	 public List<Map<String, Object>> getAllAgentStructure() {
			String vaSql = "SELECT * "
					+ "FROM TSLI_GA_AGENT_STRUCTURE";
			List<Map<String, Object>> result = db.queryList(vaSql);
	        
	        if (result != null && result.size() > 0) {
	            return result;
	        } else {
	            return null;
	        }
	 }
	 
	 /**
	  * this get result PC of AL
	  * @return
	  */
	 public List<Map<String, Object>> getPcOfAL(String clos_ym) {
			String vaSql = ""+
					"	select ( NVL((SELECT MAX(TEMP_ID) FROM TSLI_TEMP_AL),0) + ROWNUM ) AS TEMP_ID,a.*							\n"+	
					"	from                                                                                                        \n"+
					"	(select b.PE_NO_COL,b.PE_NO_SM, b.CLOS_YM as PO_CLOS_YM , b.AFYP, b.ARYP2 , ( b.AFYP + b.ARYP2 ) as PC_AL , \n"+
					"	b.PO_NO ,b.RIDER_CD                                                                                         \n"+
					"	FROM (SELECT PE_NO ,clos_ym, fst_enco_mms FROM tsli_ga_agent_structure                                      \n"+
					"	WHERE dty_cd ='S10240203' and LCNS_RNWL_YMD is not null                                                     \n"+
					"	) a LEFT JOIN tsli_ga_commission_positive  b on a.pe_no = b.PE_NO_COL                                       \n"+
					"	WHERE a.clos_ym = b.CLOS_YM                                                                                 \n"+
					"	AND  a.clos_ym = ?                                                                                    		\n"+
					"	AND (b.CLOS_YM,b.PE_NO_COL,b.PE_NO_SM,b.PO_NO,b.RIDER_CD) NOT IN	 										\n"+
					"	(                                                                   										\n"+
					"	select PO_CLOS_YM as CLOS_YM,PE_NO_COL,PE_NO_SM,PO_NO,RIDER_CD       										\n"+
					"	from TSLI_TEMP_AL_FREELOOK                                           										\n"+
					"	)                                                                    										\n"+    
					"	                                                                                                            \n"+
					"	union                                                                                                       \n"+
					"	                                                                                                            \n"+
					"	select p.PE_NO_COL,p.PE_NO_SM,p.CLOS_YM as PO_CLOS_YM, sum(p.AFYP) as AFYP,                                 \n"+
					"	sum(p.ARYP2) as ARYP2,  sum(p.AFYP)+ sum(p.ARYP2) as PC_AL,p.PO_NO,                                         \n"+
					"	p.RIDER_CD                                                                                                  \n"+
					"	from tsli_ga_commission_positive p                                                                          \n"+
					"	where p.CLOS_YM = ? 																						\n"+ 
					"  and p.DTY_CD in ( 'S10240205', 'S10240206' )                                                                 \n"+
					"	AND (p.CLOS_YM,p.PE_NO_COL,p.PE_NO_SM,p.PO_NO,p.RIDER_CD) NOT IN	 										\n"+
					"	(                                                                    										\n"+
					"	select PO_CLOS_YM as CLOS_YM,PE_NO_COL,PE_NO_SM,PO_NO,RIDER_CD       										\n"+
					"	from TSLI_TEMP_AL_FREELOOK                                           										\n"+
					"	)                                                                    										\n"+    
					"	group by p.PE_NO_COL,p.PE_NO_SM,p.CLOS_YM,p.PO_NO ,p.RIDER_CD                                               \n"+
					"	) a                                                                                                         \n";
			
			
//			System.out.println("getPcOfAL "+clos_ym+"  \n"+ vaSql);
			
			List<Map<String, Object>> result = db.queryList(vaSql , clos_ym,clos_ym);
	        
	        if (result != null && result.size() > 0) {
	            return result;
	        } else {
	            return null;
	        }
	 }
	
	 public String getProductionPartForOE() {
			String vaSql = "";

			Map<String, Object> result = db.querySingle(vaSql);
			if (result != null) {
				return (String) result.get("PRODUCTION_PART").toString()
						+"/"+result.get("PC").toString()+"/"
						+result.get("ARYP").toString()+"/"
						+result.get("PERSISTENCY").toString();
			} else {
				return null;
			}
		}
	 
	 
	 public List<Map<String, Object>> getPersistencyBonusForAVP(String paPeNo , String paYear) {
		 
			String vaSql = "SELECT  ryp.RYP2_6 ,  persistency.PERSISTENCY_RATE , persistency.BONUS_RATE , ryp.STATUS ,"
					+ "CASE "
							+ "											         WHEN persistency.PERSISTENCY_RATE BETWEEN 0 AND 59.99 THEN '0% - 59.99%'"
							+ "											         WHEN persistency.PERSISTENCY_RATE BETWEEN 60 AND 69.99 THEN '60% - 69.99%'"
							+ "											         WHEN persistency.PERSISTENCY_RATE BETWEEN 70 AND 79.99 THEN '70% - 79.99%'"
							+ "											         WHEN persistency.PERSISTENCY_RATE BETWEEN 80 AND 89.99 THEN '80% - 89.99%'"
							+ "											         WHEN persistency.PERSISTENCY_RATE BETWEEN 90 AND 100 THEN '90% - 100%'"
							+ "											       END AS PERSISTENCY_RATE_LANGE"
					+ "											 FROM (SELECT SUM(SUM_RYC) AS RYP2_6 , STATUS"
					+ "											       FROM (SELECT commission_positive.pe_no_mm,sum(NVL(commission_positive.ryc,0)) AS SUM_RYC, ' ' AS STATUS"
					+ "											             FROM TSLI_GA_COMMISSION_POSITIVE commission_positive"
					+ "											             WHERE 	commission_positive.pe_no_mm = ?"
					+ "                                          AND ( commission_positive.cont_mms BETWEEN 13 and 72 )"
					+ "                                          AND commission_positive.pe_no_soli = commission_positive.pe_no_col "
					+ "                                          AND TO_DATE(commission_positive.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM')"
							+ "											             GROUP BY commission_positive.pe_no_mm"
							+ "											             UNION ALL"
							+ "											             SELECT commission_positive.pe_no_mm,0.5*sum(NVL(commission_positive.ryc,0))  AS SUM_RYC, 'transfer' AS STATUS"
							+ "											             FROM TSLI_GA_COMMISSION_POSITIVE commission_positive"
							+ "											             WHERE 	commission_positive.pe_no_mm = ?"
							+ "                                          AND ( commission_positive.cont_mms BETWEEN 13 and 72 )"
							+ "                                          AND commission_positive.pe_no_soli <> commission_positive.pe_no_col"
							+ "                                          AND TO_DATE(commission_positive.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM')"
							+ "											             GROUP BY commission_positive.pe_no_mm)GROUP BY STATUS) ryp ,"
							+ "											(SELECT CASE "
							+ "											         WHEN (sum(PRST_BIZRT)/sum(SOLI_BIZRT) * 100) BETWEEN 0 AND 59.99 THEN 0"
							+ "											         WHEN (sum(PRST_BIZRT)/sum(SOLI_BIZRT) * 100) BETWEEN 60 AND 69.99 THEN 0.05"
							+ "											         WHEN (sum(PRST_BIZRT)/sum(SOLI_BIZRT) * 100) BETWEEN 70 AND 79.99 THEN 0.1"
							+ "											         WHEN (sum(PRST_BIZRT)/sum(SOLI_BIZRT) * 100) BETWEEN 80 AND 89.99 THEN 0.15"
							+ "											         WHEN (sum(PRST_BIZRT)/sum(SOLI_BIZRT) * 100) BETWEEN 90 AND 100 THEN 0.2"
							+ "											       END AS BONUS_RATE ,"
							+ "											       (sum(PRST_BIZRT)/sum(SOLI_BIZRT) * 100) AS PERSISTENCY_RATE"
							+ "											       FROM TSLI_PERSISTENCY "
							+ "											       where PE_NO = ?"
							+ "                                    AND PRST_RTE_ITM_CD = 'S11030B10207'"
							+ "                                    AND TO_DATE(CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM')"
							+ "											       Order by PE_NO,PRST_RTE_ITM_CD,Description) persistency";

			List<Map<String, Object>> result = db.queryList(vaSql,paPeNo,paPeNo,paPeNo);
			if (result != null) {
				return result;
			} else {
				return null;
			}
		}
	 
	 public List<Map<String, Object>> findAVPData( String paPeNo,String paYear ) {
		String vaSql = "  SELECT pe_no_mm,clos_ym,sum(AFYP) AS AFYP  FROM tsli_ga_commission_positive "
				+ "  WHERE pe_no_mm = ? AND (clos_ym BETWEEN '"+paYear+"01' AND '"+paYear+"12') AND pe_no_mm IN "
				+ " (SELECT DISTINCT(pe_no) FROM tsli_ga_agent_structure WHERE dty_cd ='S10240202') "
				+ " GROUP BY pe_no_mm,clos_ym  ORDER BY clos_ym ASC";
		List<Map<String, Object>> result = db.queryList(vaSql,paPeNo);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}
	 
	 public List<Map<String, Object>> findVPData( String paPeNo) {
	
		 	String conditon = "";
		 	if(!paPeNo.equals("%")){
		 		conditon = "AND PE_NO = '"+paPeNo+"'";
		 	}
			String vaSql = " SELECT DISTINCT PE_NO , nm_title_thai, nm_thai, surnm_thai FROM TSLI_GA_AGENT_STRUCTURE WHERE DTY_CD='S10240201' "+conditon;

			List<Map<String, Object>> result = db.queryList(vaSql);
			if (result != null) {
				return result;
			} else {
				return null;
			}
		}
	 
	 public List<Map<String, Object>> findAVPData( String paPeNo) {
			
		 	String conditon = "";
		 	if(!paPeNo.equals("")){
		 		conditon = "AND PE_NO = '"+paPeNo+"'";
		 	}
			String vaSql = " SELECT DISTINCT PE_NO , nm_title_thai, nm_thai, surnm_thai FROM TSLI_GA_AGENT_STRUCTURE WHERE DTY_CD='S10240202' "+conditon;

			List<Map<String, Object>> result = db.queryList(vaSql);
			if (result != null) {
				return result;
			} else {
				return null;
			}
		}
	 
	 public List<Map<String, Object>> showDataByMonthYear (String paYearMonth){
		 String vaSql ="select b.PE_NO_SM,st.NM_TITLE_THAI,st.NM_THAI,st.SURNM_THAI,b.BONUS_FL as PC_AL "
		 		+ " from TSLI_GA_AGENT_STRUCTURE st "
		 		+ " inner join  ( select a.PE_NO_SM,a.BONUS_FL from  ( "
		 		+ " select clos_ym,pe_no_sm,OLD_ACTIVE_AMOUNT,NEW_ACTIVE_AMOUNT,pc_old,pc_new,bonus_net,bonus_fl "
		 		+ " from TSLI_TEMP_AL_BONUS_DETAIL "
		 		+ " where  RECAL_YM =  "+paYearMonth+"  "
		 		+ " and clos_ym = "+paYearMonth+" "
		 		+ " group by clos_ym,pe_no_sm,OLD_ACTIVE_AMOUNT,NEW_ACTIVE_AMOUNT,pc_old,pc_new,bonus_net,bonus_fl "
		 		+ "order by clos_ym,pe_no_sm "
		 		+ ") a left outer join "
		 		+ "( "
		 		+ "select pe_no_sm,sum(bonus_net-bonus_fl) as paid_more "
		 		+ "from TSLI_TEMP_AL_BONUS_DETAIL "
		 		+ "where  RECAL_YM =  "+paYearMonth+"  "
		 		+ " group by pe_no_sm "
		 		+ " ) b on a.pe_no_sm = b.pe_no_sm "
		 		+ " ) b "
		 		+ "on st.PE_NO = b.PE_NO_SM "
		 		+ " where st.DTY_CD = 'S10240203'  "
			 	+ "	and st.CLOS_YM = "+paYearMonth+" ";
		 List<Map<String, Object>> result = db.queryList(vaSql);
		 if (result != null) {
				return result;
			} else {
				return null;
			}
	 }
	 public List<Map<String, Object>> showDataByMonthYearPeNo (String paYearMonth,String paPeNo){
		 String vaSql ="select b.PE_NO_SM,st.NM_TITLE_THAI,st.NM_THAI,st.SURNM_THAI,b.BONUS_FL as PC_AL "
			 		+ " from TSLI_GA_AGENT_STRUCTURE st "
			 		+ " inner join  ( select a.PE_NO_SM,a.BONUS_FL from  ( "
			 		+ " select clos_ym,pe_no_sm,OLD_ACTIVE_AMOUNT,NEW_ACTIVE_AMOUNT,pc_old,pc_new,bonus_net,bonus_fl "
			 		+ " from TSLI_TEMP_AL_BONUS_DETAIL "
			 		+ " where  RECAL_YM =  "+paYearMonth+"  "
			 		+ " and clos_ym = "+paYearMonth+" "
			 		+ " group by clos_ym,pe_no_sm,OLD_ACTIVE_AMOUNT,NEW_ACTIVE_AMOUNT,pc_old,pc_new,bonus_net,bonus_fl "
			 		+ "order by clos_ym,pe_no_sm "
			 		+ ") a left outer join "
			 		+ "( "
			 		+ "select pe_no_sm,sum(bonus_net-bonus_fl) as paid_more "
			 		+ "from TSLI_TEMP_AL_BONUS_DETAIL "
			 		+ "where  RECAL_YM =  "+paYearMonth+"  "
			 		+ " group by pe_no_sm "
			 		+ " ) b on a.pe_no_sm = b.pe_no_sm "
			 		+ " ) b "
			 		+ "on st.PE_NO = b.PE_NO_SM "
			 		+ " where st.DTY_CD = 'S10240203'  "
				 	+ "	and st.CLOS_YM = "+paYearMonth+" "
				 	+ " and st.PE_NO LIKE '%"+paPeNo+"%' ";
		 System.out.println("showDataByMonthYearPeNo" +vaSql);
		 List<Map<String, Object>> result = db.queryList(vaSql);
		 if (result != null) {
				return result;
			} else {
				return null;
			}
	 }
	 
	 public List<Map<String, Object>> showDetails (String paYearMonth,String paPeNo){
		 String vaSql ="select s.pe_no  ,  s.nm_title_thai , s.nm_thai , s.surnm_thai  ,s.dty_cd, ( p.AFYP  +  p.ARYP2 )  as PC  "
		 		+ "from tsli_ga_agent_structure s "
		 		+ " inner join tsli_ga_commission_positive p on s.pe_no = p.pe_no_col and s.clos_ym  =  p.clos_ym "
		 		+ "where (s.dty_cd ='S10240203' or s.dty_cd ='S10240205' or s.dty_cd ='S10240206') and s.LCNS_RNWL_YMD is not null "
		 		+ "and  s.clos_ym = "+paYearMonth+" -- this paramete "
		 		+ "and  s.pe_no_sm  =  "+paPeNo+" -- this parameter"
		 		+ "order by pe_no desc";
		 System.out.println("showDetails \n"+vaSql);
		 List<Map<String, Object>> result = db.queryList(vaSql);
		 if (result != null) {
				return result;
			} else {
				return null;
			}
	 }
	 
}
